﻿﻿//////////// --- 读取excel文件并转json

// using Microsoft.Office.Interop.Excel;

using System;
using System.IO;
using System.Collections.Generic;
using OfficeOpenXml;  // dotnet add package Epplus
using Newtonsoft.Json;


namespace ReadExcel
{

    public class MyClient
    {
        public static void run()
        {
            // string f_path = @"/Users/mac/Desktop/操作记录表.xls";
            string f_path = @"data/红色动态表.xls";

            // 读取 Excel 文件
            using (ExcelPackage package = new ExcelPackage(new FileInfo(f_path)))
            {
                // 读取第一个工作表
                // BdUtils.log(package.Workbook.Worksheets);
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                // BdUtils.log("~~~ worksheet.Row: ", worksheet.Row(1).Value);
                // 读取第一行第一列的单元格
                // Console.WriteLine(worksheet.Cells[1, 1].Value);

                int rowCount = worksheet.Dimension.End.Row;
                int columnCount = worksheet.Dimension.End.Column;
                BdUtils.log($"--- rowCount: {rowCount} --- columnCount: {columnCount}");

                if (true)
                {
                    // Access the cells in the worksheet
                    var range = worksheet.Cells["A1:Z3"];

                    // Read the data from the cells
                    // var data = new List<List<object>>();
                    var headers = new List<string>();

                    for (int col = 1; col <= columnCount; col++)
                    {
                        var value = range[1, col].Value.ToString();
                        headers.Add(value);
                    }
                    // BdUtils.log($"--- headers: {headers}");
                    BdUtils.log("~~~ headers:", headers);

                    var data = new List<Dictionary<string, object>>();

                    for (int row = 2; row <= rowCount; row++)
                    {
                        // var rowData = new List<object>();
                        var rowData = new Dictionary<string, object>();

                        var dictionary = new Dictionary<string, object>();

                        for (int col = 1; col <= columnCount; col++)
                        {
                            var header_i = headers[col - 1];
                            var value = range[row, col].Value;

                            var cell = worksheet.Cells[row, col];
                            bool isDateTime = BdUtils.get_type(value) == "System.DateTime";
                            if (isDateTime)
                            {
                                value = ((DateTime)value).ToString("yyyy-MM-dd hh:mm:ss");
                                // BdUtils.log("isDateTime --- ", isDateTime, "---", header_i, value, BdUtils.get_type(value));
                            }

                            dictionary.Add(header_i, value);
                        }
                        data.Add(dictionary);
                    }
                    // BdUtils.log("--- data: ", data[0]["发布时间"]);

                    foreach (var obj in data)
                    {
                        BdUtils.log('~', obj);
                    }

                    // string json = JsonConvert.SerializeObject(data);
                    // Console.WriteLine(json);

                    var dc = BdUtils.conv_json_to_dict(data[0]);
                    BdUtils.log("--- dc:", dc);

                }
            }
        }

    }
}
